# coding: utf-8

# 生成用于金牌自动抢单的内部用户

import os
import json
import random

import set_env_path     # 请勿删除

import xlrd
from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def insert_internal_users():
    insert_internal_user_sql = text("""
        insert into user (
            mobile, nickname, area_id, is_internal, credit,
            type, gender, is_virt, created_at, updated_at
        ) values (
            :mobile, :nickname, :area_id, :is_internal, :credit,
            :type, :gender, :is_virt, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
        );
    """)

    insert_pack_sql = text("""
        insert into pack (
            uid, area_id, sms_point, accum_sms_point,
            bid_point, accum_bid_point,
            end_date, created_at, updated_at
        ) values (
            :uid, :area_id, :sms_point, :accum_sms_point,
            :bid_point, :accum_bid_point,
            :end_date, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
        );
    """)

    find_last_insert_user_id_sql = text("""
        select LAST_INSERT_ID();
    """)

    book = xlrd.open_workbook('./internal_user.xlsx')
    sheet = book.sheet_by_index(0)

    for row in range(1, sheet.nrows):
        mobile = str(sheet.cell(row, 0).value).strip().split('.')[0]
        nickname = str(sheet.cell(row, 1).value).strip()

        conn.execute(
            insert_internal_user_sql,
            mobile=mobile,
            nickname=nickname,
            area_id=440300,
            is_internal=True,
            credit=random.randint(100000, 110000),
            is_virt=True,
            type=100,
            gender=1,
        )

        new_user_id = conn.execute(
            find_last_insert_user_id_sql
        ).fetchone()[0]

        conn.execute(
            insert_pack_sql,
            uid=new_user_id,
            area_id=json.dumps([120100]),
            sms_point=10000,
            accum_sms_point=10000,
            bid_point=100000,
            accum_bid_point=100000,
            end_date='2020-01-01',
        )


if __name__ == '__main__':
    insert_internal_users()
